# CREATE TABLE `user_info` (
#   `id` int(11) ,
#   `idcard` varchar(50) ,
#   `username` varchar(50) ,
#   `realname` varchar(50) ,
#   `pwd` varchar(50) ,
#   `telphone` varchar(12) ,
#   `email` varchar(100) ,
#   `age` int(11) ,
#   `sex` varchar(20) ,
#   `address` varchar(200) ,
#   `hiredate` date ,
#   `sal` double(9,2) ,
#   `job` varchar(100) ,
#   `company` varchar(100)
# ) ;
# 编程实现：使用openpyxl在excel中写入14列2000行的数据，然后使用openpyxl读取出来并使用pymysql写入到数据库中。
# 需要创建 “basic_database_practiceV2.0.xlsx”文件

import openpyxl
import pymysql


workbook = openpyxl.load_workbook('basic_database_practiceV2.0.xlsx')       # 加载名为 'basic_database_practiceV2.0.xlsx' 的 Excel 文件以进行后续数据读取操作


if 'user_info' not in workbook.sheetnames:                                  # 检查是否存在名为 'user_info' 的工作表，若不存在则创建该工作表
    workbook.create_sheet(title='user_info')
    workbook.save('basic_database_practiceV2.0.xlsx')

worksheet = workbook['user_info']                                           # 获取名为 'user_info' 的工作表

headers= ['id', 'idcard', 'username', 'realname', 'pwd', 'telphone', 'email',  # 定义表头
          'age', 'sex', 'address', 'hiredate', 'sal', 'job', 'company']

# for col_num, header in enumerate(headers, 1):                               # 遍历表头
#     worksheet.cell(row = 1, column = col_num, value = header)               # enumerate 是遍历列表headers，col_num是列号，从一开始
#
# workbook.save('basic_database_practiceV2.0.xlsx')

existing_headers = [                                                          # 获取已存在的表头
    worksheet.cell(row = 1,column = col_num).value if worksheet.cell(row = 1,column = col_num).value is not None
    else ''                                                                   # 判断列是否为空，若为空则设置为空字符串，若不为空则保留当前值
    for col_num in range(1, len( headers)+1)
]
if headers != existing_headers:                                               # 如果表头不一致，则更新表头，否则不更新
    for col_num, header in enumerate(headers, 1 ):
        worksheet.cell(row = 1, column = col_num, value = header)
    workbook.save('basic_database_practiceV2.0.xlsx')
    print("打印成功，表头已更新")
else:
    print("打印失败，表头已经存在")

data = [
    [1, '1234567890123456', 'user1', '张三', '123456', '13800000001', 'zhangsan@example.com', 18, '男', '中国上海', '2023-01-01', 5000.00, '软件工程师', '上海公司'],
    [2, '1234567890123457', 'user2', '李四', '123456', '13800000002', 'lisi@example.com', 19, '女', '中国北京', '2023-01-02', 6000.00, '产品经理', '北京公司']
]
for i in data:
    worksheet.append(i)
workbook.save('basic_database_practiceV2.0.xlsx')
print("数据写入成功")

# 读取数据（跳过第一行）
# 有bug，如果excel表里有东西，则数据会重复写入到数据库中（未解决）
rows = list(worksheet.iter_rows(min_row = 2, values_only = True))

conn = pymysql.connect(host='localhost',
                       user='root',
                       password='123456',
                       database='hr_sql',
                       charset = 'utf8'
                       )
# 三引号可以使sql 多行输入，易读
sql =  """
INSERT INTO user_info (
    id, idcard, username, realname, pwd, telphone, email, age, sex, address,
    hiredate, sal, job, company
) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
"""
try:
    with conn.cursor() as cursor:
        cursor.executemany(sql, rows)
        conn.commit()
        print(f"数据写入成功，打印了{len(rows)}数据")
except Exception as e:
    conn.rollback()
    print(f"数据写入失败：{e}")
finally:
    cursor.close()
    conn.close()



